﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using Newtonsoft.Json;
using System.Configuration;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using System.Dynamic;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using Microsoft.JScript;



namespace TradeWeb.Controllers
{
    public class CollectMoneyController : Controller
    {
        //
        // GET: /CollectMoney/

        public ActionResult Index()
        {
            return View();
        }

        //查询应收款情况列表－[分页+模糊查询]
        public string GetCollectMoneyListPages(string strUserCode, string strPageIndex, string strPageSizes, string strSearchWhere)
        {
            string jsonResult = string.Empty;
            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            try
            {
                //打开数据库连接
                sqlConn.Open();
                DataSet dsResult = new DataSet();


                string strWhere = string.Empty;
                //添加模糊查询的条件------查询记录总数目的时候，也要添加模糊查询的条件。（模糊查询与分页互不干扰）
                if (!string.IsNullOrEmpty(strSearchWhere.Trim()))
                {
                    strSearchWhere = strSearchWhere.Trim();
                    strSearchWhere = Microsoft.JScript.GlobalObject.decodeURIComponent(strSearchWhere);
                    string strSearch = " ( InvoiceNum  LIKE '%" + strSearchWhere + "%' OR ExpPactNums LIKE '%" + strSearchWhere + "%' OR CustPactNums  LIKE '%" + strSearchWhere + "%' OR CNUserName LIKE '%" + strSearchWhere + "%' OR  ClientName  LIKE '%" + strSearchWhere + "%' OR CNDeptName  like '%" + strSearchWhere + "%' )";
                    strWhere += strSearch;
                }
                if (!string.IsNullOrEmpty(strWhere))
                {
                    strWhere = " and " + strWhere;
                }


                //查询记录的数目
                string strSqlCount = "SELECT COUNT(*) Row_Count FROM V_InvoiceDOC where  ISDel=0  AND  CONVERT(VARCHAR(10),InvoiceDate,20)>=CONVERT(VARCHAR(10),DATEADD(YEAR,-1,GETDATE()),20) AND CONVERT(VARCHAR(10),InvoiceDate,20)<=CONVERT(VARCHAR(10),GETDATE(),20) AND NoCustMoney<>0 " + strWhere;
                SqlCommand sqlComCount;
                SqlDataAdapter sqlAdaCount;
                sqlComCount = new SqlCommand(strSqlCount, sqlConn);
                sqlAdaCount = new SqlDataAdapter(sqlComCount);
                sqlAdaCount.Fill(dsResult, "T_Count");



                //查询列表的数据
                string strSqlResult = "SELECT * FROM (SELECT ROW_NUMBER() over(order by InvoiceNum) as colnum,isdel,InvoiceNum,Creater,CreaterName,UserCode,CNUserName,Deptcode,CNDeptName,InvoiceDate,ExpPactNums,CustPactNums,ClientCode,ClientName,AimPort,ExportPort,Payment,OpenShipDate,PriceClause,TotalSaleMoney,CustMoney,NoCustMoney  FROM V_InvoiceDOC  where ISDel=0  AND  CONVERT(VARCHAR(10),InvoiceDate,20)>=CONVERT(VARCHAR(10),DATEADD(YEAR,-1,GETDATE()),20) AND CONVERT(VARCHAR(10),InvoiceDate,20)<=CONVERT(VARCHAR(10),GETDATE(),20) AND NoCustMoney<>0 " + strWhere + " ) tab_1";
                SqlCommand sqlComResult;
                SqlDataAdapter sqlAdaResult;
                if (string.IsNullOrEmpty(strPageSizes))
                {
                    //默认的查询条数是10；
                    strPageSizes = "10";
                }
                string strSearchPage = string.Empty;
                if (!string.IsNullOrEmpty(strPageIndex))
                {
                    strSearchPage = " WHERE colnum>(" + strPageIndex + "-1)*" + strPageSizes + " AND colnum<=" + strPageIndex + "*" + strPageSizes + "";
                }
                if (!string.IsNullOrEmpty(strSearchPage))
                {
                    strSqlResult = strSqlResult + strSearchPage;
                }
                sqlComResult = new SqlCommand(strSqlResult, sqlConn);
                sqlAdaResult = new SqlDataAdapter(sqlComResult);
                sqlAdaResult.Fill(dsResult, "T_CollectMoney");

                sqlConn.Close();
                jsonResult = "{\"success\":true  ,\"data\":" + JsonConvert.SerializeObject(dsResult) + "  ,\"message\":\"正常\"}";
                return jsonResult;
            }
            catch (Exception ex)
            {
                jsonResult = "{\"success\":false  ,\"data\":{}   ,\"message\":\"" + ex.Message + "\"}";
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            return jsonResult;
        }
    }
}
